package com.dao;

import com.bean.Department;
import com.common.DbConnect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDao {
    public void addDepartment(Department department){
        Connection cn= DbConnect.getConnection();
        PreparedStatement st = null;
        String sql = "insert into department(dnumber,dname)" +
                " values(?,?)";
        try {
            st = cn.prepareStatement(sql);

            st.setString(1, department.getDnumber());
            st.setString(2, department.getDname());
            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }
    }
    public void deleteDepartment(String dnumber){
        Connection cn= DbConnect.getConnection();
        PreparedStatement st = null;
        String sql = "delete from Department where dnumber = " + dnumber;
        try {
            st = cn.prepareStatement(sql);
            st.execute();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }
    }
    public void updateDepartment(Department department){
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        try {
            String sql = "update department set dname=? where dnumber=?";
            st = cn.prepareStatement(sql);

            st.setString(1,department.getDname());
            st.setString(2,department.getDnumber());
            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }
    }
    public boolean getDepartment(String deptid){
        Department department=null;
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return false;
        try {
            //4.执行sql
            String sql = "select * from department where dnumber=?";
            st = cn.prepareStatement(sql);

            st.setString(1,deptid);
            rs = st.executeQuery();
            if(!rs.next()) {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return true;
    }
    public List<Department> getDepartmentList(){
        ArrayList<Department> departments = new ArrayList<Department>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            String sql = "select * from department";
            st = cn.prepareStatement(sql);

            rs = st.executeQuery();
            while (rs.next()) {
                Department department = new Department(rs.getString("dnumber"),rs.getString("dname"));
                departments.add(department);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return departments;
    }
}
